package dao;

import dao.imp.IEmployeeDao;
import entity.Employee;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import util.MysqlConnUtil;

import java.util.ArrayList;
import java.util.List;

/**
 * @program: personnel_management_system
 * @description: 员工表数据库操作
 * @author: Mr.Sun
 * @create: 2020-05-23 18:01
 **/

public class EmployeeDao implements IEmployeeDao {
    private JdbcTemplate template = new JdbcTemplate(MysqlConnUtil.getDataSource());

    public EmployeeDao() throws Exception {
    }

    @Override
    public boolean insert(Employee emp) {
        boolean res = false;
        String sql = "INSERT INTO employee(id_card,name,gender,residence,degree,dept,age) VALUES(?,?,?,?,?,?,?)";

        int count = template.update(sql,emp.getId_card(),emp.getName(),emp.getGender(),emp.getResidence(),emp.getDegree(),emp.getDept(),emp.getAge());
        res = count == 1;
        return res;
    }

    @Override
    public boolean update(Employee emp) {
        boolean res =false;
        String sql = "UPDATE employee SET id_card = ?,name = ? ,gender = ? ,residence = ? ,degree = ?,dept = ? ,age = ? ,authorized = ? ,marriage = ? WHERE id = ?";

        int count = template.update(sql,emp.getId_card(),emp.getName(),emp.getGender(),emp.getResidence(),emp.getDegree(),emp.getDept(), emp.getAge(),emp.getAuthorized(),emp.getMarriage(),emp.getId());
        return count == 1;
    }

    @Override
    public boolean delete(Employee emp) {
        boolean res =false;
        String sql = "DELETE FROM employee WHERE id = ?";
        int count = template.update(sql,emp.getId());
        return count == 1;
    }

    @Override
    public List<Employee> selectAll() {

        String sql = "select * from employee";
        List<Employee> emps = template.query(sql, new BeanPropertyRowMapper<Employee>(Employee.class));
        return emps;
    }

    @Override
    public List<Employee> select(Employee emp) {
        return null;
    }

    @Override
    public List<Employee> selectByAge(Integer startAge, Integer endAge, Integer startWork, Integer endWork) {
        String sql = "SELECT * FROM employee e,(SELECT emp_id,sum(work_time) work_time FROM contract group by emp_id having sum( work_time)>="+startWork+" and sum( work_time)<="+endWork+") a\n" +
                " where e.id = a.emp_id and e.age>= "+startAge+" and e.age <= "+endAge+";";
        //startWork,endWork,startAge,endAge
        List<Employee> emps = template.query(sql,new BeanPropertyRowMapper<Employee>(Employee.class));
        return emps;
    }



}
